package model;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;

public class Database {
	/**
	 * The database connection.
	 */
	private Connection conn;

	private static final int BATCH_PER_PALLET = 54; //5400 cookies for one pallet, 100 cookies for one batch

	public Database() {

	}

	/**
	 * Open a connection to the database, using the specified user name and
	 * password.
	 * 
	 * @param userName
	 *            The user name.
	 * @param password
	 *            The user's password.
	 * @return true if the connection succeeded, false if the supplied user name
	 *         and password were not recognized. Returns false also if the JDBC
	 *         driver isn't found.
	 */
	public boolean openConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(
					"jdbc:mysql://puccini.cs.lth.se/" + "db15", "db15",
					"chapter2144");
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * Close the connection to the database.
	 */
	public void closeConnection() {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
		}
		conn = null;
	}

	/**
	 * Check if the connection to the database has been established
	 * 
	 * @return true if the connection has been established
	 */
	public boolean isConnected() {
		return conn != null;
	}

	/**
	 * Adds a database element to the table Pallets
	 * 
	 * @param pallet
	 *            the object to be added.
	 * @return true if successful, false otherwise
	 */
	public boolean addPallet(Pallet pallet) {
		try {
			String selectPallNbrsCode = pallet.maxNbrOfPalletsCode();
			String currentPallNbrForOrder = pallet.currentPallNbrForOrder();
			PreparedStatement ps;
			ResultSet rs;
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(selectPallNbrsCode);
			rs = ps.executeQuery();
			int maxNbrOfPallets = -1;
			while (rs.next()) {
				maxNbrOfPallets = Integer
						.parseInt(rs.getString("nbrOfPallets"));
			}

			ps = conn.prepareStatement(currentPallNbrForOrder);
			rs = ps.executeQuery();
			int currentNbrOfPallets = -1;
			while (rs.next()) {
				String countedPallets = rs.getString("count(pallNbr)");
				currentNbrOfPallets = Integer.parseInt(countedPallets);
				System.out.println("maxNbrOfPallets: "+maxNbrOfPallets);
				System.out.println("currentNbrOfPallets: "+currentNbrOfPallets);
			}
			if (maxNbrOfPallets != -1 && currentNbrOfPallets != -1) {
				if (!(currentNbrOfPallets < maxNbrOfPallets)) {
					conn.rollback();
					conn.setAutoCommit(true);
					System.out.println("too many pallets added");
					System.out.println("maxNbrOfPallets: "+maxNbrOfPallets);
					System.out.println("currentNbrOfPallets: "+currentNbrOfPallets);
					return false;
				}
			} else {
				System.out.println("Error with fetching value from database");
				System.out.println("maxNbrOfPallets: "+maxNbrOfPallets);
				System.out.println("currentNbrOfPallets: "+currentNbrOfPallets);
				conn.setAutoCommit(true);
				return false;
			}
			//calculating needed ingredients from recipe
			String selectNeededIngredients = pallet.selectNeededIngredients();
			ps = conn.prepareStatement(selectNeededIngredients);
			rs = ps.executeQuery();
			HashMap<String, Integer> recipe = new HashMap<String, Integer>();
			String getAllIngredients = "select ingredientName, weight from Ingredients";
			if(rs.next()){
				String ingredient = rs.getString("ingredientName");
				recipe.put(ingredient, 
						Integer.parseInt(rs.getString("weight")));
				getAllIngredients += " where ingredientName='"+ingredient+"'"; 
			}
			while (rs.next()) {
				String ingredient = rs.getString("ingredientName");
				recipe.put(ingredient, 
						Integer.parseInt(rs.getString("weight")));
				getAllIngredients += " or ingredientName='"+ingredient+"'"; 
			}
			getAllIngredients += ";"; 
			//checking that needed ingredients from recipe is not above available ingredients
			
			ps = conn.prepareStatement(getAllIngredients);
			rs = ps.executeQuery();
			HashMap<String, Integer> availableIngredients = new HashMap<String, Integer>();
			while (rs.next()) {
				String ingredient = rs.getString("ingredientName");
				if(recipe.containsKey(ingredient)){
					int neededForPallet = recipe.get(ingredient)*BATCH_PER_PALLET;
					int availableOfIngredient = Integer.parseInt(rs.getString("weight"));
					availableIngredients.put(ingredient, availableOfIngredient);
					if(!(neededForPallet <= availableOfIngredient)){
						System.err.println("ingredient: "+ingredient);
						System.err.println("neededForPallet: "+neededForPallet);
						System.err.println("availableOfIngredient: "+availableOfIngredient);
						conn.setAutoCommit(true);
						return false;
					}	
				}
					
			}
			//updating storage with ingredients
			for(String ingredient : recipe.keySet()) {
				int remainingWeight = availableIngredients.get(ingredient)-recipe.get(ingredient);
				String updateStorage = "update Ingredients set weight="
					+remainingWeight +" where ingredientName='"+ingredient+"';";
				ps = conn.prepareStatement(updateStorage);
				ps.execute();
			}			
			//adding new pallet to table
			String sqlStmt = pallet.addPalletCode();
			executeSqlCode(sqlStmt);
			conn.setAutoCommit(true);
			System.out.println("add method ended");
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * Search for pallets
	 * 
	 * @param searchCategory
	 *            is what category you are searching for
	 * @param searchValue
	 *            this is the value you are looking for
	 * @return
	 */
	public ArrayList<Pallet> searchDb(String searchCategory,
			String searchValue, boolean searchBlocked) {
		String sqlStmt = null;
		String blocked;
		if (searchBlocked) {
			blocked = " pallNbr in (select pallNbr from blockedPallets)";
		} else {
			blocked = " pallNbr not in (select pallNbr from blockedPallets)";
		}
		if (searchValue.length() == 0) {
			sqlStmt = "select * from pallets where "+blocked+";";
		} else {
			if (searchCategory == "pallNbr" || searchCategory == "orderNbr") {
				sqlStmt = "select * from pallets where " + searchCategory + "="
						+ searchValue +" and "+ blocked + ";";
			} else {// TODO Fix so that no numbers are allowed here
				try{
					Integer.parseInt(searchValue);
				}catch(NumberFormatException e){
					sqlStmt = "select * from pallets where " + searchCategory
						+ " like '" + searchValue +"%' and "+ blocked + ";";
				
			}
		}
		}
		ArrayList<Pallet> pallNbrs = new ArrayList<Pallet>();
		if(sqlStmt != null){
		try {
			PreparedStatement ps = conn.prepareStatement(sqlStmt);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String id = rs.getString("pallNbr");
				String type = rs.getString("cookieName");
				String location = rs.getString("location");
				String produced = rs.getString("producedTime");
				String delivered = rs.getString("timeDelivered");
				String order = rs.getString("orderNbr");
				pallNbrs.add(new Pallet(id, type, location, produced,
						delivered, order));
			}
			return pallNbrs;
		} catch (SQLException e) {
			return null;
		}
		
		}
		return null;
	}

	private void executeSqlCode(String sqlStmt) {
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement(sqlStmt);
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public Pallet getPallet(int palletId) {
		String sqlStmt = "select * from pallets where pallNbr=" + palletId
				+ ";";
		try {
			PreparedStatement ps = conn.prepareStatement(sqlStmt);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				String id = rs.getString("pallNbr");
				String type = rs.getString("cookieName");
				String location = rs.getString("location");
				String produced = rs.getString("producedTime");
				String delivered = rs.getString("timeDelivered");
				String order = rs.getString("orderNbr");
				return new Pallet(id, type, location, produced, delivered,
						order);
			}
			return null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Get the latest pallet
	 * 
	 * @return the latest pallet created.
	 */
	public Pallet getLatestPallet() {
		String sqlStmt = "select * from Pallets where pallNbr= (select max(pallNbr) from Pallets);";
		try {
			PreparedStatement ps = conn.prepareStatement(sqlStmt);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				String id = rs.getString("pallNbr");
				String type = rs.getString("cookieName");
				String location = rs.getString("location");
				String produced = rs.getString("producedTime");
				String delivered = rs.getString("timeDelivered");
				String order = rs.getString("orderNbr");
				Pallet pallet = new Pallet(id, type, location, produced,
						delivered, order);
				return pallet;
			}
			return null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Block a pallet
	 * 
	 * @param pallNbr
	 *            the pallnumber to be blocked
	 * @return returns if the blocking were successfull or not.
	 */
	public boolean blockPallet(int pallNbr, String fromDate, String toDate) {
		String sqlStmt = "select cookieName from Pallets where pallNbr = "+pallNbr+";";
		String cookieName;
		PreparedStatement ps;
		ResultSet rs;
		try {
			ps = conn.prepareStatement(sqlStmt);
			rs = ps.executeQuery();
			if(rs.next()){
				cookieName = rs.getString("cookieName");
				System.out.println(cookieName);
			}else{
				return false; //couldn't find a pallet with that palletNbr
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		sqlStmt = "select pallNbr from Pallets where cookieName = '"+cookieName+
				"' and datediff(producedTime,'"+toDate+"') < 0 and pallNbr not in" +
						" (select pallNbr from blockedPallets) and datediff(producedTime,'"+fromDate+"') > 0;";
		ArrayList<Integer> palletNbrsToBeBlocked = new ArrayList<Integer>();
		try {
			ps = conn.prepareStatement(sqlStmt);
			rs = ps.executeQuery();
			while(rs.next()){
				palletNbrsToBeBlocked.add(Integer.parseInt(rs.getString("pallNbr")));
			}
			for(Integer i: palletNbrsToBeBlocked){
				System.out.println(i);
				conn.prepareStatement("insert into blockedPallets(pallNbr) values("+i+");").execute();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;		
	}
	
	public boolean unBlockPallet(int pallNbr, String fromDate, String toDate){
		String sqlStmt = "select cookieName from Pallets where pallNbr = "+pallNbr+";";
		String cookieName;
		PreparedStatement ps;
		ResultSet rs;
		try {
			ps = conn.prepareStatement(sqlStmt);
			rs = ps.executeQuery();
			if(rs.next()){
				cookieName = rs.getString("cookieName");
				System.out.println(cookieName);
			}else{
				return false; //couldn't find a pallet with that palletNbr
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		sqlStmt = "select pallNbr from Pallets where cookieName = '"+cookieName+
		"' and datediff(producedTime,'"+toDate+"') < 0 and pallNbr in" +
				" (select pallNbr from blockedPallets) and datediff(producedTime,'"+fromDate+"') > 0;";
		
		ArrayList<Integer> palletNbrsToBeBlocked = new ArrayList<Integer>();
		try {
			ps = conn.prepareStatement(sqlStmt);
			rs = ps.executeQuery();
			while(rs.next()){
				palletNbrsToBeBlocked.add(Integer.parseInt(rs.getString("pallNbr")));
			}
			for(Integer i: palletNbrsToBeBlocked){
				System.out.println(i);
				conn.prepareStatement("delete from blockedPallets where pallNbr ="+i+";").execute();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;		
	
	}

//	public String[] getOrders() {
//		String sqlStmt = "select orderNbr from orders;"; 
//		//TODO: select if (currentNbrOfOrders < maxNumberOfOrders)
//		PreparedStatement ps;
//		ResultSet rs;
//		ArrayList<String> orders = new ArrayList<String>();
//		try {
//			ps = conn.prepareStatement(sqlStmt);
//			rs = ps.executeQuery();
//			while(rs.next()){
//				String order = rs.getString("cookieName");
//				orders.add(order);
//				System.out.println(order);
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		return null;
//	}

}